# coding=utf-8
# 调用exchangelib 发送邮件
from exchangelib import DELEGATE, Account, Credentials, Message, Mailbox, HTMLBody, Configuration, NTLM
from exchangelib.protocol import BaseProtocol, NoVerifyHTTPAdapter
from datetime import datetime
from openpyxl import load_workbook
import urllib3
# 读取、编辑ini配置文件
import configparser

urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
# 此句用来消除ssl证书错误，exchange服务器使用自签证书需加上
BaseProtocol.HTTP_ADAPTER_CLS = NoVerifyHTTPAdapter


class ExchangeEmail:
    def __init__(self, username, password, server, primary_smtp_address):
        self.username = username
        self.password = password
        self.server = server
        self.primary_smtp_address = primary_smtp_address

    def send_email(self, to, subject, body):
        creds = Credentials(
            username=self.username,
            password=self.password
        )
        config = Configuration(
            server=self.server,
            auth_type=NTLM
        )
        account = Account(
            primary_smtp_address=self.primary_smtp_address,
            config=config,
            credentials=creds,
            autodiscover=True,
            access_type=DELEGATE
        )
        m = Message(
            account=account,
            subject=subject,
            body=HTMLBody(body),
            to_recipients=[Mailbox(email_address=to)]
        )
        m.send()
# 接收邮箱，主题，内容


def round_up(value):
    return round(value * 100) / 100.00


if __name__ == '__main__':
    # 获取配置
    config = configparser.RawConfigParser()
    config.read('config.ini', encoding="utf-8-sig")
    UserName = config.get('User', 'UserName')
    Department = config.get('User', 'Department')
    Company_name = config.get('Company', 'Company_name')
    Company_address = config.get('Company', 'Company_address')
    Company_phone = config.get('Company', 'Company_phone')
    send_username = config.get('Email', 'SendUser')
    send_password = config.get('Email', 'SendPassword')
    email_server = config.get('Email', 'EmailServer')
    send_addr = config.get('Email', 'SendEmailAdd')
    FileName = config.get('Email', 'FileName')
    SheetName = config.get('Email', 'SheetName')

    # 确认配置情况
    print('请确认当前配置如下：')
    print('邮箱服务器：' + email_server + '\n' +
          '发送方账号：' + send_username + '\n' +
          '发送方邮箱地址：' + send_addr + '\n' +
          '工资条文件名（含后缀名）：' + FileName + '\n' +
          '当前工资条sheet：' + SheetName + '\n'
          )
    option = input('配置正确请输入‘1’，不正确请输入‘2’：')
    if option == '1':
        Email = ExchangeEmail(send_username, send_password, email_server, send_addr)
        # 定义相关模板
        css_template = '''
        <style type="text/css">
             .style1
             {
                 height: 14.25pt;
                 width: 60pt;
                 color: black;
                 font-size: 8.0pt;
                 font-weight: 400;
                 font-style: normal;
                 text-decoration: none;
                 font-family: 微软雅黑, sans-serif;
                 text-align: general;
                 vertical-align: middle;
                 white-space: nowrap;
                 border: .2pt solid windowtext;
                 padding-left: 1px;
                 padding-right: 1px;
                 padding-top: 1px;
                 background: #D7E4BC;
             }
             .style2
             {
             color: black;
                 font-size: 9.0pt;
                 font-weight: 400;
                 font-style: normal;
                 text-decoration: none;
                 font-family: 微软雅黑, sans-serif;
                 text-align: general;
                 vertical-align: middle;
                 white-space: nowrap;
                 border: .2pt solid windowtext;
                 padding-left: 1px;
                 padding-right: 1px;
                 padding-top: 1px;
                 background: #eeeecc;
             }
        </style>
        '''
        table_head_temple = '<table border="1" color="black"><tr>'
        table_content = ''
        table_foot_temple = '</tr></table>'
        foot_temple = '<br>各位领导同事好！<br><br>工资条内容保密，请勿外泄！<br><br>如对工资条有任何疑问，请直接回复本邮件' \
                      '。<br><br>顺祝工作愉快！<br><br><br><br><br><br>' + UserName + '<br>' + Department + '<br>' \
                      '&nbsp;<br>' + Company_name + '<br>地&nbsp;&nbsp;址：' + Company_address + '<br>电&nbsp;&nbsp;话：' \
                      + Company_phone + '<br>E-mail：' + send_addr + '<br><br><br><br>'
        # 遍历excel数据表格
        try:
            wb = load_workbook(FileName, data_only=True)
            sh = wb[SheetName]
            # 获取当前年月份
            now = datetime.now()
            year = now.year
            month = now.month
            # 生成标题
            subject = str(year) + '年' + str(month) + '月工资—'
            # 获取列数
            cols_count = len(list(sh.columns))
            list_col_name = []
            for col in range(1, len(list(sh.columns))):
                col_name = sh.cell(row=1, column=col).value
                list_col_name.append(col_name)
            # 遍历获取数据
            print("开始处理工资条数据，共计数量" + str(len(list(sh.rows)) - 1) + "条………………………………")
            for row in range(2, len(list(sh.rows)) + 1):
                name = sh.cell(row=row, column=1).value
                email_addr = sh.cell(row=row, column=cols_count).value
                for offset_col in range(0, cols_count-1):
                    if sh.cell(row=row, column=offset_col+1).value != 0 and \
                            sh.cell(row=row, column=offset_col+1).value != '' and \
                            sh.cell(row=row, column=offset_col+1).value is not None:
                        table_title_temple = '<td class="style1">' + list_col_name[offset_col] + '</td>'
                        table_head_temple = table_head_temple + table_title_temple
                        column_value = sh.cell(row=row, column=offset_col+1).value
                        if type(column_value) is not str:
                            column_value = round_up(column_value)
                        table_content_temple = '<td class="style2">' + \
                                               str(column_value) + '</td>'
                        table_content = table_content + table_content_temple
                subject = subject + name
                email_content = css_template + table_head_temple + '</tr><tr>' + table_content + table_foot_temple + foot_temple
                Email.send_email(email_addr, subject, email_content)
                print(name + " 已发送，当前已完成" + str(row - 1) + "/" + str(len(list(sh.rows)) - 1) + "条")
                table_head_temple = '<table border="1" color="black"><tr>'
                table_content = ''
                subject = str(year) + '年' + str(month) + '月工资—'
        except Exception as e:
            print(e)
